package de.unihannover.hci.portraitquiz.database;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import de.unihannover.hci.portraitquiz.PortraitQuiz;
import de.unihannover.hci.portraitquiz.R;
import de.unihannover.hci.portraitquiz.model.Score;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "portraitQuiz";
	private static final String TABLE_HIGHSCORE = "highscore";

	// Highscore's Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_CATEGORY = "category";
	private static final String KEY_POINTS = "points";
	private static final String KEY_DATE = "datetime";
	private static final String KEY_ANSWERS_CORRECT = "answers_correct";
	private static final String KEY_ANSWERS_PLAYED = "answers_played";

	private Context context;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_CategoryS_TABLE = "CREATE TABLE " + TABLE_HIGHSCORE + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_CATEGORY + " INTEGER," + KEY_POINTS + " INTEGER," + KEY_DATE + " TEXT," + KEY_ANSWERS_CORRECT + " INTEGER," + KEY_ANSWERS_PLAYED + " INTEGER" + ")";
		Log.w("PortraitQuiz", "Create Table: " + CREATE_CategoryS_TABLE);
		db.execSQL(CREATE_CategoryS_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_HIGHSCORE);
		onCreate(db);
	}

	public void addHighScoreEntry(Score score) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_CATEGORY, score.getCategory());
		values.put(KEY_POINTS, score.getPoints());
		values.put(KEY_DATE, score.getDatetime().getTime());
		values.put(KEY_ANSWERS_CORRECT, score.getAnswers_correct());
		values.put(KEY_ANSWERS_PLAYED, score.getAnswers_played());

		db.insert(TABLE_HIGHSCORE, null, values);
		db.close();
	}

	public Score getHighscoreEntry(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_HIGHSCORE, new String[] { KEY_ID, KEY_CATEGORY, KEY_POINTS, KEY_DATE, KEY_ANSWERS_CORRECT, KEY_ANSWERS_PLAYED }, KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Score highscoreItem = new Score(Integer.parseInt(cursor.getString(0)), Integer.parseInt(cursor.getString(1)),
				Integer.parseInt(cursor.getString(2)), new Date(Long.parseLong(cursor.getString(3))),
				Integer.parseInt(cursor.getString(4)), Integer.parseInt(cursor.getString(5)));
		return highscoreItem;
	}

	public List<Score> getAllHighscoreItems() {
		List<Score> highscoreItemList = new ArrayList<Score>();
		String selectQuery = "SELECT * FROM " + TABLE_HIGHSCORE + " WHERE " + KEY_CATEGORY + "="
				+ ((PortraitQuiz) context).getCurrentCategory().getId() + " ORDER BY " + KEY_POINTS + " DESC";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// TODO Liste auf z.B. 20 Elemente begrenzen! Unten eingefügt, muss getestet werden. 
		int i = 0;
		if (cursor.moveToFirst()) {
			do {
				Score highscoreItem = new Score();
				highscoreItem.setId(Integer.parseInt(cursor.getString(0)));
				highscoreItem.setCategory(Integer.parseInt(cursor.getString(1)));
				highscoreItem.setPoints(Integer.parseInt(cursor.getString(2)));
				highscoreItem.setDatetime(new Date(Long.parseLong(cursor.getString(3))));
				highscoreItem.setAnswers_correct(Integer.parseInt(cursor.getString(4)));
				highscoreItem.setAnswers_played(Integer.parseInt(cursor.getString(5)));
				highscoreItemList.add(highscoreItem);
				i++;
			} while (cursor.moveToNext() && i<20);
		}

		return highscoreItemList;
	}

	public int updateHighscoreItem(Score highscoreItem) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_CATEGORY, highscoreItem.getCategory());
		values.put(KEY_POINTS, highscoreItem.getPoints());
		values.put(KEY_DATE, highscoreItem.getDatetime().getTime());
		values.put(KEY_ANSWERS_CORRECT, highscoreItem.getAnswers_correct());
		values.put(KEY_ANSWERS_PLAYED, highscoreItem.getAnswers_played());

		return db.update(TABLE_HIGHSCORE, values, KEY_ID + " = ?",
				new String[] { String.valueOf(highscoreItem.getId()) });
	}

	public void deleteHighscoreItem(Score highscoreItem) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_HIGHSCORE, KEY_ID + " = ?", new String[] { String.valueOf(highscoreItem.getId()) });
		db.close();
	}

	public int getHighscoreCount() {
		String countQuery = "SELECT  * FROM " + TABLE_HIGHSCORE;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		return cursor.getCount();
	}

//	public int getCategoryRanking(int id) {
//		String query = "SELECT MAX(" + KEY_POINTS + ") FROM " + TABLE_HIGHSCORE;
//		SQLiteDatabase db = this.getReadableDatabase();
//		Cursor cursor = db.rawQuery(query, null);
//
//		int ranking = 0;
//		int maxPoints = 0;
//
//		if (cursor.moveToFirst()) {
//			maxPoints = cursor.getInt(0);
//		}
//
//		cursor.close();
//
//		query = "SELECT MAX(" + KEY_POINTS + ") FROM " + TABLE_HIGHSCORE + " WHERE " + KEY_CATEGORY + "=" + id
//				+ " GROUP BY " + KEY_CATEGORY;
//		cursor = db.rawQuery(query, null);
//
//		if (cursor.moveToFirst()) {
//			int maxFromCategory = cursor.getInt(0);
//			ranking = (int) (100.0 / maxPoints * maxFromCategory); //Division durch 0 möglich.
//		}
//		cursor.close();
//
//		return getRankingDrawable(ranking);
//	}
//
//	public int getCurrentRanking(int points) {
//		String query = "SELECT MAX(" + KEY_POINTS + ") FROM " + TABLE_HIGHSCORE;
//		SQLiteDatabase db = this.getReadableDatabase();
//		Cursor cursor = db.rawQuery(query, null);
//
//		int ranking = 0;
//		int maxPoints = 0;
//
//		if (cursor.moveToFirst()) {
//			maxPoints = cursor.getInt(0);
//		}
//
//		cursor.close();
//
//		ranking = (int) (100.0 / maxPoints * points);
//
//		return getRankingDrawable(ranking);
//	}
//
//	private int getRankingDrawable(int ranking) {
//		if (ranking > 80) {
//			ranking = R.drawable.stars5;
//		} else if (ranking > 60) {
//			ranking = R.drawable.stars4;
//		} else if (ranking > 40) {
//			ranking = R.drawable.stars3;
//		} else if (ranking > 20) {
//			ranking = R.drawable.stars2;
//		} else if (ranking > 0) {
//			ranking = R.drawable.stars1;
//		} else {
//			ranking = R.drawable.stars0;
//		}
//		return ranking;
//	}

	public int getHighestScoreForCat(int cat) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT * FROM " + TABLE_HIGHSCORE + " WHERE " + KEY_CATEGORY + "=" + cat + " ORDER BY "
				+ KEY_POINTS + " DESC";

		Cursor cursor = db.rawQuery(selectQuery, null);

		int score = 0;
		if (cursor != null && cursor.moveToFirst()) {
			score = cursor.getInt(2);
			cursor.close();
		}
		return score;
	}
}